import sqlite3
import os
from .userlib import *

"""
Returns all albums
"""
def get_albums():
  conn = sqlite3.connect(os.environ["DOCUMENT_ROOT"] 
      + "/PictureShare/pygallery.sqlite")
  conn.row_factory = sqlite3.Row
  album_cur = conn.cursor()
  album_cur.execute("""
  select
    a.album_id,
    a.name,
    a.is_public,
    a.user_id,
    u.username
  from albums a, users u
  where a.user_id = u.user_id
  """)
  return (album_cur, conn)

"""
Returns album with specified id
"""
def get_album(album_id):
  conn = sqlite3.connect(os.environ["DOCUMENT_ROOT"] 
      + "/PictureShare/pygallery.sqlite")
  conn.row_factory = sqlite3.Row
  album_cur = conn.cursor()
  album_cur.execute("""
    select a.* from albums a where album_id = ?
    """, (album_id,))
  album = album_cur.fetchone()
  album_cur.close()
  conn.close()
  return album

"""
Get all pictures in specified album
"""
def get_album_pictures(album_id):
  conn = sqlite3.connect(os.environ["DOCUMENT_ROOT"] 
      + "/PictureShare/pygallery.sqlite")
  conn.row_factory = sqlite3.Row
  picture_cur = conn.cursor()
  picture_cur.execute("""
  select p.* from pictures p where p.album_id = ? 
  """, (album_id,))
  return (picture_cur, conn)

"""
Get all deleted pictures in specified album
"""
def get_deleted_pictures(album_id):
  conn = sqlite3.connect(os.environ["DOCUMENT_ROOT"] 
      + "/PictureShare/pygallery.sqlite")
  conn.row_factory = sqlite3.Row
  picture_cur = conn.cursor()
  picture_cur.execute("""
  select p.* from pictures_deleted p where p.album_id = ? 
  """, (album_id,))
  return (picture_cur, conn)

"""
Get first picture in specified album
"""
def get_album_preview(album_id):
  conn = sqlite3.connect(os.environ["DOCUMENT_ROOT"] 
      + "/PictureShare/pygallery.sqlite")
  conn.row_factory = sqlite3.Row
  picture_cur = conn.cursor()
  picture_cur.execute("""
  select p.file_name from pictures p where p.album_id = ? 
  """, (album_id,))
  picture = picture_cur.fetchone()
  picture_cur.close()
  conn.close()
  return picture

"""
Sets the album visibility to new value
"""
def change_visibility(album_id, new_val):
  conn = sqlite3.connect(os.environ["DOCUMENT_ROOT"] 
      + "/PictureShare/pygallery.sqlite")
  conn.row_factory = sqlite3.Row
  album_cur = conn.cursor()
  album_cur.execute("""
    update albums SET is_public = ? WHERE album_id = ?
    """, (new_val, album_id,))
  album_cur.close()
  conn.commit()
  conn.close()
  
"""
Adds a new album in db
returns the new album_id
"""
def add_album(user_id, album_name, is_public):
  # Add the album to the db
  conn = sqlite3.connect(os.environ["DOCUMENT_ROOT"] 
      + "/PictureShare/pygallery.sqlite")
  session_cur = conn.cursor()
  session_cur.execute("""
    insert into albums (album_id, user_id, name, is_public) 
    values (NULL, ?, ?, ?)
    """, (user_id, album_name, is_public))
  session_cur.close()
  new_album_id = session_cur.lastrowid
  conn.commit()
  conn.close()
  user = userlib.get_user_by_id(user_id)
  # Create directories
  try:
    os.makedirs(os.environ["DOCUMENT_ROOT"]
            + "/PictureShare/users/"
            + user["username"] + "/"
            + "album"+ str(new_album_id) + "/")
    os.makedirs(os.environ["DOCUMENT_ROOT"]
            + "/PictureShare/users/"
            + user["username"] + "/"
            + "albumthumb"+ str(new_album_id) + "/")
  except OSError:
    pass # directory probably already exists
  return new_album_id
  
"""
Deletes album
"""
def remove_album(album_id, user_id):
  # Add the album to the db
  conn = sqlite3.connect(os.environ["DOCUMENT_ROOT"] 
      + "/PictureShare/pygallery.sqlite")
  session_cur = conn.cursor()
  session_cur.execute("""delete from albums where album_id=?""", [album_id])
  session_cur.close()
  conn.commit()
  conn.close()
  user = userlib.get_user_by_id(user_id)
  # Create directories
  os.rmdir(os.environ["DOCUMENT_ROOT"]
          + "/PictureShare/users/"
          + user["username"] + "/"
          + "album"+ str(album_id) + "/")
  os.rmdir(os.environ["DOCUMENT_ROOT"]
          + "/PictureShare/users/"
          + user["username"] + "/"
          + "albumthumb"+ str(album_id) + "/")

